﻿using System;
using System.IO;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Xml;
using System.Security.Principal;
using Microsoft.Win32;

using EdgeStatz.ESSystems.Logs;

namespace EdgeStatz.ESSystems.Objects
{
    public interface GeneratedForm
    {
        string ParentKey { set; }
        void Save();
        bool ChangesPending();
    }

    public interface GeneratedTab
    {
        object[] Save();
        bool ChangesPending();
    }

    public static class GlobalObjectsSQL
    {
        private static string connectionString = "";
        private static string serverName = "localhost";
        private static string databaseName = "master";
        private static string userId = null;
        private static string password = null;

        public static string ConnectionStirng
        {
            get { return connectionString; }
        }

        public static string ServerName
        {
            set
            {
                serverName = value;
                SetConnectionString();
            }
            get { return serverName; }
        }

        public static string DatabaseName
        {
            set
            {
                databaseName = value;
                SetConnectionString();
            }
            get { return databaseName; }
        }
        
        public static string UserId
        {
            set
            {
                userId = value;
                SetConnectionString();
            }
            get { return userId; }
        }

        public static string Password
        {
            set
            {
                password = value;
                SetConnectionString();
            }
            get { return password; }
        }

        public static SqlConnection SqlConnection
        {
            get { return new SqlConnection(ConnectionStirng); }
        }

        public static void SetConnectionString(string server, string database, string uid, string pwd)
        {
            serverName = server;
            databaseName = database;
            userId = uid;
            password = pwd;
            SetConnectionString();
        }

        public static void SetConnectionString(string server, string uid, string pwd)
        {
            serverName = server;
            userId = uid;
            password = pwd;
            databaseName = "master";
            SetConnectionString();
        }

        public static void SetConnectionString(string server, string database)
        {
            serverName = server;
            databaseName = database;
            userId = null;
            password = null;
            SetConnectionString();
        }

        public static void SetConnectionString()
        {
            ParseString();
            connectionString = 
                "server=" + serverName + "; " +
                "initial catalog=" + databaseName + ";" +
                ((userId == null ? "" : ("uid=" + userId + "; ")) +
                (password == null ? "" : ("password=" + password + "; "))) + 
                "MultipleActiveResultSets=True; Integrated Security=SSPI";

            if (GlobalSystemSettings.LogSql)
                LogWriter.WriteToLog("Set Connection string to -> " + connectionString, LogType.Sql);
        }
        
        public static void SetConnectionString(Provider provider)
        {
            serverName = provider.Server;
            databaseName = provider.Database;
            userId = provider.UserId;
            password = provider.Password;     
            SetConnectionString();            
        }

        public static bool TestConnection(Provider provider)
        {
            if (provider == null)
                return false;
            try
            {
                using (SqlConnection conn = new SqlConnection("server=" + provider.Server + ";" +
                    "initial catalog=master;" +
                    ((provider.UserId == null ? "" : ("uid=" + provider.UserId + "; ")) +
                    (provider.Password == null ? "" : ("password=" + provider.Password + "; "))) +
                    "MultipleActiveResultSets=True; Integrated Security=SSPI"))
                {
                    conn.Open();
                    if (conn.State == System.Data.ConnectionState.Open)
                        return true;
                    return false;
                }
            }
            catch (SqlException e)
            { return false; }
        }
        
        private static bool ParseString()
        {
            if (serverName != null && (serverName.Contains("'") || serverName.Contains(";") ||
                //serverName.Contains(")") || serverName.Contains("(") ||
                serverName.Contains("=") || serverName.Contains(" ")))
                throw new Exception("Invalid character in server name: " + serverName);
            if (databaseName != null && (databaseName.Contains("'") || databaseName.Contains(";") ||
                databaseName.Contains(")") || databaseName.Contains("(") ||
                databaseName.Contains("=") || databaseName.Contains(" ")))
                throw new Exception("Invalid character in database name: " + databaseName);
            if (userId != null && (userId.Contains("'") || userId.Contains(";") ||
                userId.Contains(")") || userId.Contains("(") ||
                userId.Contains("=") || userId.Contains(" ")))
                throw new Exception("Invalid character in user Id: " + userId);
            if (password != null && (password.Contains("'") || password.Contains(";") ||
                password.Contains(")") || password.Contains("(") ||
                password.Contains("=") || password.Contains(" ")))
                throw new Exception("Invalid character in password: " + password);
            return true;
        }
        
        private static string ParseString(string value)
        {
            foreach (char c in new char[] { ';', '(', ')', '=', ':', '\'', '\"', '+', '-', '&', '%', '#', '!', '?' })
            {
                value = value.Replace(c, new char());
            }
            return value;
        }

        /// <summary>
        /// Executes a Transact-SQL statement. Returns nothing
        /// </summary>
        /// <param name="stringCommand"></param>
        public static void ExecuteNonQueryCommand(string stringCommand)
        {
            if (GlobalSystemSettings.LogSql)
                LogWriter.WriteToLog("Execute Non-Query -> " + stringCommand, LogType.Sql);

            using (SqlConnection conn = SqlConnection)
            {
                conn.Open();
                string[] delimitedSqlCommand = stringCommand.Split(
                    new string[1] { "GO" }, StringSplitOptions.None);

                foreach (string sqlCommand in delimitedSqlCommand)
                {
                    using (SqlCommand command = new SqlCommand(sqlCommand, conn))
                    {
                        command.ExecuteNonQuery();
                    }
                }
                conn.Close();
            }
        }

        public static object ExecuteScalarQueryCommand(string stringCommand)
        {
            if (GlobalSystemSettings.LogSql)
                LogWriter.WriteToLog("Execute Scalar Query -> " + stringCommand, LogType.Sql);

            using (SqlConnection conn = SqlConnection)
            {
                conn.Open();
                object returnVal = null;
                using (SqlCommand command = new SqlCommand(stringCommand, conn))
                {
                    returnVal = command.ExecuteScalar();
                }
                conn.Close();
                return returnVal;
            }
        }

        public static object[] ExecuteReaderQueryCommand(string stringCommand)
        {
            if (GlobalSystemSettings.LogSql)
                LogWriter.WriteToLog("Execute Reader Query -> " + stringCommand, LogType.Sql);

            using (SqlConnection conn = SqlConnection)
            {
                conn.Open();
                List<object> returnVal = new List<object> { };
                using (SqlCommand command2 = new SqlCommand(stringCommand, conn))
                {
                    SqlDataReader tmp = command2.ExecuteReader();
                    while (tmp.Read())
                    {
                        object[] array = new object[tmp.VisibleFieldCount];
                        tmp.GetValues(array);
                        returnVal.Add(array);
                    }
                }
                conn.Close();
                return returnVal.ToArray();
            }
        }

        /// <summary>
        /// Checks If a given object exists in the database
        /// </summary>
        /// <param name="objectName">Name of object</param>
        /// <param name="type">Type (e.g. USER_TABLE, FOREIGN_KEY_CONSTRAINT, DEFAULT_CONSTRAINT etc)</param>
        /// <returns>True if object exists; False otherwise</returns>
        public static bool CheckObjectExists(string objectName, string type)
        {
            string cmdText = "SELECT [type_desc] FROM sys.objects WHERE object_id = OBJECT_ID('[dbo].[" + objectName + "]')";

            object nRet = ExecuteScalarQueryCommand(cmdText);
            if (nRet == null) { return false; }
            else
            {
                if (((string)nRet).ToUpper().Equals(type.ToUpper())) { return true; }
                else { return false; }
            }
        }

        public static object[] ExecuteSingleTableRead(string[] columns, string table, string where)
        {
            return ExecuteSingleTableRead(columns, table, where, null);
        }
        
        public static object[] ExecuteSingleTableRead(string[] columns, string table, string where, string orderby)
        {
            string cmdText = "SELECT ";
            if (columns == null || columns.Length == 0)
                cmdText += "*, ";
            else
                foreach (string col in columns)
                    cmdText += col + ", ";
            cmdText = cmdText.Remove(cmdText.Length - 2, 2);
            cmdText += "\nFROM " + table;
            if (where != null && where.Trim() != "")
                cmdText += "\nWHERE " + where;
            if (orderby != null && orderby.Trim() != "")
                cmdText += "\nORDER BY " + orderby;
            return ExecuteReaderQueryCommand(cmdText);
        }

        public static object ExecuteSingleRowInsert(string table, string[] columns, object[] values, string keyColumn) 
        {
            string cmdText = "INSERT INTO " + "[" + table + "]" + "(";
            
            foreach (string col in columns)
                cmdText += "[" + col + "]" + ", ";
            cmdText = cmdText.Remove(cmdText.Length - 2, 2);
            cmdText += ")";
            cmdText += "VALUES (";

            foreach (object val in values)
            {
                cmdText += ReturnForDataEntry(val);
                cmdText += ", ";
            }
            cmdText = cmdText.Remove(cmdText.Length - 2, 2);            
            cmdText += ")";

            if (keyColumn != null)
            {
                string trigText = "CREATE TRIGGER tr_INSERT " + "ON [" + table + "]" +
                    "FOR INSERT AS " + "SELECT " + keyColumn + " AS 'key' FROM inserted go";
                ExecuteNonQueryCommand(trigText);
            }
            object ret;
            try
            {
                ret = ExecuteScalarQueryCommand(cmdText);
            }
            catch (Exception e)
            {
                if (keyColumn != null)
                    ExecuteNonQueryCommand("DROP TRIGGER [dbo].[tr_INSERT]");
                throw e;
            }
            
            if (keyColumn != null)
                ExecuteNonQueryCommand("DROP TRIGGER [dbo].[tr_INSERT]");
            return ret;
        }
        
        public static void ExecuteSingleRowUpdate(string table, string[] columns, object[] values, string where)
        {
            string cmdText = "UPDATE " + "[" + table + "]" + " SET ";
            if (columns.Length != values.Length)
                throw new Exception("SQL Update Error: Number of columns must equal the number of values.");

            for (int i = 0; i < columns.Length; i++)
            {
                cmdText += "[" + columns[i] + "]" + " = " + ReturnForDataEntry(values[i]) + ", ";
            }
            cmdText = cmdText.Remove(cmdText.Length - 2, 2);
            cmdText += " WHERE " + where;
            ExecuteNonQueryCommand(cmdText);
        }
        
        public static void ExecuteSingleRowUpdate(string table, string[] columns, object[] values, string keyColumn, object keyValue) 
        {
            string where = keyColumn + "=";
            where += ReturnForDataEntry(keyValue);
            ExecuteSingleRowUpdate(table, columns, values, where);
        }

        public static void ExecuteMultipleRowInsert(string table, string[] columns, object[] value_array)
        {
            if (value_array.Length == 0)
                return;
            string cmdText = "INSERT INTO " + "[" + table + "]" + "(";

            foreach (string col in columns)
                cmdText += "[" + col + "]" + ", ";
            cmdText = cmdText.Remove(cmdText.Length - 2, 2);
            cmdText += ")";

            for (int i = 0; i < value_array.Length;i++ )
            {
                cmdText += "\nSELECT ";
                object[] insert_val = (object[])value_array[i];
                for (int j = 0; j < insert_val.Length;j++ )
                {
                    cmdText += ReturnForDataEntry(insert_val[j]);
                    if (j < insert_val.Length - 1)
                        cmdText += ", ";
                }
                if (i < value_array.Length - 1)
                    cmdText += "\nUNION ALL";
            }
            ExecuteNonQueryCommand(cmdText);
        }

        public static object AuthenticateUser(string username, string password)
        {
            ParseString(username);
            ParseString(password);
            return ExecuteScalarQueryCommand("SELECT SecurityLevel From StdUser WHERE UserID = '" + 
                username + "' AND Password = '" + password + "'");
        }

        public static string ReturnForDataEntry(object value)
        {
            if (value == null)
                return "NULL";
            switch (value.GetType().ToString().ToLower())
            {
                case "system.string":
                    return "'" + value.ToString() + "'";
                case "system.guid":
                    return "'" + value.ToString() + "'";
                case "system.int":
                    return value.ToString();
                case "system.float":
                    return value.ToString();
                case "system.double":
                    return value.ToString();
                case "system.decimal":
                    return value.ToString();
                case "system.long":
                    return value.ToString();
                case "system.bool":
                    if ((bool)value)
                        return "1";
                    return "0";
                case "system.datetime":
                    DateTime dt = (DateTime)value;
                    return "'" + dt.Year + "-" + dt.Month + "-" + dt.Day + " " + dt.Hour + ":" + dt.Minute + ":" + dt.Second + "'";
                    break;
                case "system.dbnull":
                    return "NULL";
                default:
                    return "'" + value.ToString() + "'";
            }
        }
    }

    public static class XmlModelFile
    {
        public static void CreateFile(string path, string modelName, string systemPath, bool overWrite)
        {
            if (!overWrite && File.Exists(path))
                throw new FileLoadException("File Already Exists");
            XmlDocument newXmlDoc = new XmlDocument();
            XmlNode node = newXmlDoc.CreateElement("Model");
            XmlNode node2 = newXmlDoc.CreateElement("Database");
            
            XmlAttribute dbnameAtr = newXmlDoc.CreateAttribute("Name");
            dbnameAtr.Value = modelName;
            node2.Attributes.Append(dbnameAtr);
            XmlAttribute dbpathAtr = newXmlDoc.CreateAttribute("Path");
            dbpathAtr.Value = systemPath;
            node2.Attributes.Append(dbpathAtr);

            node.AppendChild(node2);
            node.AppendChild(newXmlDoc.CreateElement("Structure"));
            node.AppendChild(newXmlDoc.CreateElement("Forms"));

            XmlAttribute nameAtr = newXmlDoc.CreateAttribute("Name");
            nameAtr.Value = modelName;
            node.Attributes.Append(nameAtr);
            XmlAttribute pathAtr = newXmlDoc.CreateAttribute("Path");
            pathAtr.Value = systemPath;
            node.Attributes.Append(pathAtr);

            newXmlDoc.AppendChild(node);

            newXmlDoc.Save(path);
        }
    }
    
    public static class GlobalSystemSettings
    {
        private static string modelName;
        private static string databaseName;
        private static string modelFilePath;
        private static string systemFolderPath;
        private static bool logSql = false;
        
        public static string ModelName
        {
            get { return modelName; }
            set { modelName = value; }
        }
        public static string DatabaseName
        {
            get { return databaseName; }
            set { databaseName = value; }
        }
        public static string ModelFilePath
        {
            get { return modelFilePath; }
            set { modelFilePath = value; }
        }
        public static string SystemFolderPath
        {
            get { return systemFolderPath; }
            set { systemFolderPath = value; }
        }

        public static bool LogSql
        {
            get { return logSql; }
            set { logSql = value; }
        }

        public static bool IsAnAdministrator()
        {
            WindowsIdentity identity =
               WindowsIdentity.GetCurrent();
            WindowsPrincipal principal =
               new WindowsPrincipal(identity);
            return principal.IsInRole
               (WindowsBuiltInRole.Administrator);
        }
    }

    public static class GlobalRegistrySettings
    {
        private static string appPath;
        //private static bool login;
        private static int modelCount;
        private static string[] modelNames;
        private static string[] modelPaths;
        private static string licenseKey;

        public static string ApplicationPath
        {
            get { return appPath; }
        }
        //public static bool Login
        //{
        //    get { return login; }
        //}
        public static int ModelCount
        {
            get { return modelCount; }
        }
        public static string[] ModelNames
        {
            get { return modelNames; }
        }
        public static string[] ModelPaths
        {
            get { return modelPaths; }
        }
        public static string LicenseKey
        {
            get { return licenseKey; }
        }

        public static void Load()
        {
            RegistryKey regkey = GetRegistryRoot(false);
            
            object path = regkey.GetValue("path");
            if (path == null)
                throw new Exception("Registry key [...EdgeStatz ltd\\EdgeStatz\\Path : Reg_SZ] doesn't exist.");
            appPath = (string)path;

            object license = regkey.GetValue("license");
            if (license == null)
                throw new Exception("Registry key [...EdgeStatz ltd\\EdgeStatz\\License : Reg_SZ] doesn't exist.");
            licenseKey = (string)license;

            //object log = regkey.GetValue("login");
            //if (log == null)
            //{
            //   regkey.SetValue("Login", 0);
            //}
            //else
            //{
            //    if ((int)log == 1)
            //        login = true;
            //    else
            //        login = false;
            //}
            
            modelNames = regkey.GetSubKeyNames();
            modelPaths = new string[modelNames.Length];
            if (modelNames.Length > 0)
            {
                modelCount = 0;
                foreach (string modname in modelNames)
                {
                    RegistryKey modkey = regkey.OpenSubKey(modname, false);
                    object mp = modkey.GetValue("modelpath");
                    modelPaths[modelCount] = mp == null ? null : (string)mp;
                    modelCount++;
                }
            }
            else
            {
                modelNames = null;
                modelPaths = null;
            }
        }

        public static void CreateNewModelKey(string name, string path)
        {
            if (!GlobalSystemSettings.IsAnAdministrator())
                return;

            RegistryKey regkey = GetRegistryRoot(true);

            bool keyexists = regkey.OpenSubKey(name) == null ? false : true;
            if (!keyexists)
            {
                regkey.CreateSubKey(name);    
            }            
            regkey = regkey.OpenSubKey(name, true);
            regkey.SetValue("ModelPath", path);
        }        

        public static void DeleteModelKey(string name)
        {
            RegistryKey regkey = GetRegistryRoot(true);
            
            regkey.DeleteSubKey(name);
        }

        private static RegistryKey GetRegistryRoot(bool writable)
        {
            RegistryKey regkey;
            for (int i = 0; i < 4; i++)
            {
                try
                {
                    switch (i)
                    {
                        case 0:
                            regkey = Registry.LocalMachine;
                            regkey = regkey.OpenSubKey("SOFTWARE", writable);
                            regkey = regkey.OpenSubKey("EdgeStatz ltd", writable);
                            regkey = regkey.OpenSubKey("EdgeStatz", writable);
                            return regkey;
                        case 1:
                            regkey = Registry.LocalMachine;
                            regkey = regkey.OpenSubKey("SOFTWARE", writable);
                            regkey = regkey.OpenSubKey("Wow6432Node", writable);
                            regkey = regkey.OpenSubKey("EdgeStatz ltd", writable);
                            regkey = regkey.OpenSubKey("EdgeStatz", writable);
                            return regkey;
                        case 2:
                            regkey = Registry.CurrentUser;
                            regkey = regkey.OpenSubKey("SOFTWARE", writable);
                            regkey = regkey.OpenSubKey("EdgeStatz ltd", writable);
                            regkey = regkey.OpenSubKey("EdgeStatz", writable);
                            return regkey;
                        case 3:
                            regkey = Registry.CurrentUser;
                            regkey = regkey.OpenSubKey("SOFTWARE", writable);
                            regkey = regkey.OpenSubKey("Wow6432Node", writable);
                            regkey = regkey.OpenSubKey("EdgeStatz ltd", writable);
                            regkey = regkey.OpenSubKey("EdgeStatz", writable);
                            return regkey;
                    }
                }
                catch (NullReferenceException e)
                {
                    if(i == 3)
                        throw new Exception("Registry key [...\\EdgeStatz ltd\\EdgeStatz] doesn't exist." + "\n\n" + e.Message);
                }                
            }
            return null;
        }            
    }


}
